CREATE OR REPLACE package cust_hsd_strings as
/*
   Barbara Boehmer (baboehme@hotmail.com)
   http://www.merriampark.com/ldplsql.htm
   Levenshtein distance is a measure of the similarity between two strings, which we will refer to 
   as the source string (s) and the target string (t). The distance is the number of deletions, 
   insertions, or substitutions required to transform s into t.
*/
   function levenshtein_distance 
   (
      p_source_string   IN VARCHAR2,
      p_target_string   IN VARCHAR2
   ) return number deterministic;
   
   function valid_email(p_email in varchar2) return varchar2 deterministic;
end;
/
CREATE OR REPLACE package body cust_hsd_strings as

FUNCTION levenshtein_distance
  (p_source_string   IN VARCHAR2,
   p_target_string   IN VARCHAR2)
  RETURN                NUMBER
  DETERMINISTIC
AS
  v_length_of_source    NUMBER := NVL (LENGTH (p_source_string), 0);
  v_length_of_target    NUMBER := NVL (LENGTH (p_target_string), 0);
  TYPE mytabtype IS     TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  column_to_left        mytabtype;
  current_column        mytabtype;
  v_cost                NUMBER := 0;
BEGIN
  IF v_length_of_source = 0 THEN
    RETURN v_length_of_target;
  ELSIF v_length_of_target = 0 THEN
    RETURN v_length_of_source;
  ELSE
    FOR j IN 0 .. v_length_of_target LOOP
      column_to_left(j) := j;
    END LOOP;
    FOR i IN 1.. v_length_of_source LOOP
      current_column(0) := i;
      FOR j IN 1 .. v_length_of_target LOOP
        IF SUBSTR (p_source_string, i, 1) =
           SUBSTR (p_target_string, j, 1)
        THEN v_cost := 0;
        ELSE v_cost := 1;
        END IF;
        current_column(j) := LEAST (current_column(j-1) + 1,
                                    column_to_left(j) + 1,
                                    column_to_left(j-1) + v_cost);
      END LOOP;
      FOR j IN 0 .. v_length_of_target  LOOP
        column_to_left(j) := current_column(j);
      END LOOP;
    END LOOP;
  END IF;
  RETURN current_column(v_length_of_target);
END levenshtein_distance;

function valid_email(p_email in varchar2) return varchar2 deterministic
as
l_return varchar2(1);
begin
   select 
      case
         when p_email is null then 'N'
         --this regexp is not quite rfc compliant but many email services are not either.  See E-mail_Address on Wikipedia.
         when 
            p_email not like '%..%'
            and regexp_instr(p_email,'^ *[a-zA-Z0-9._&+=-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4} *$') >= 1 
            and regexp_instr(p_email,'^ *\.') = 0
            and p_email not like '%.@%'
            and p_email not like '%@.%'
               then 'Y'
         else 'N'
      end
   into l_return
   from dual;
   return l_return;
end
;

end; --package
/
